using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Xml;
using System.Collections;
using System.IO.Packaging;

namespace LogAnalyzerIIS.LogClasses
{
	/// <summary>
	/// Parent class for log loaders and OpenXML statistics processor  
	/// </summary>
	public abstract class LogConverter
	{
		/// <summary>
		/// identificators to distinguish log column types
		/// </summary>
		public enum ColumnType
		{
			Date,
			Time,
			String,
			Number,
			Formula
		}

		/// <summary>
		/// start date of the processed logs
		/// </summary>
		public DateTime StartDate = DateTime.MaxValue;

		/// <summary>
		/// end date of the processed log
		/// </summary>
		public DateTime EndDate = DateTime.MinValue;

		// Open XML schema URLs
		protected const string schemaMain = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
		protected const string schemaRelationships = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships";

		// list of possible log columns and column types
		protected static List<string> outputColumns;
		protected static List<ColumnType> outputColumnTypes;
		protected XmlDocument worksheetXML;

		/// <summary>
		/// Constructor for common loader
		/// </summary>
		public LogConverter()
		{
			InitialiseOutputColumns();
		}

		private void InitialiseOutputColumns()
		{
			outputColumns = new List<string>();
			outputColumnTypes = new List<ColumnType>();

			outputColumns.Add("Date");                     // 0
			outputColumnTypes.Add(ColumnType.Date);
			outputColumns.Add("Time");                     // 1
			outputColumnTypes.Add(ColumnType.Time);
			outputColumns.Add("Client IP");                // 2
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("User Name");                // 3
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Service Name");             // 4
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Server Name");              // 5
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Server IP");                // 6
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Server Port");              // 7
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("Method");                   // 8
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("URI Stem");                 // 9
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("URI Query");                // 10
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Protocol Status");          // 11
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("Win32 Status");             // 12
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("Bytes Sent");               // 13
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("Bytes Retrieved");          // 14
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("Time Taken");               // 15
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("Protocol Version");         // 16
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Host");                     // 17
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("User Agent");               // 18
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Cookie");                   // 19
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Referer");                  // 20
			outputColumnTypes.Add(ColumnType.String);
			outputColumns.Add("Bytes Transmitted");        // 21
			outputColumnTypes.Add(ColumnType.Number);
			outputColumns.Add("UNKNOWN DATA");             // 22
			outputColumnTypes.Add(ColumnType.String);
		}

		protected internal static void AddSchemaAttribute(XmlElement root, string schema, string nameSpace)
		{
			XmlAttribute nsAttribute = root.OwnerDocument.CreateAttribute("xmlns", nameSpace, @"http://www.w3.org/2000/xmlns/");

			nsAttribute.Value = schema;

			root.Attributes.Append(nsAttribute);
		}

		/// <summary>
		/// Each implementing class must provide a method which adds a worksheet to a spreadsheet file,
		/// </summary>
		/// <param name="sharedStringsOutput"></param>
		/// <param name="inputFilePath"></param>
		/// <returns>an XML Document</returns>
		protected abstract XmlDocument CreateWorksheetXML(List<string> sharedStringsOutput, string inputFilePath);

		/// <summary>
		///   Converts text log to Excel OpenXML log
		/// </summary>
		/// <param name="inputFilePath">
		///   Input file path (ex*.log).
		/// </param>
		/// <param name="outputFilePath">
		///   Output file path (*.xlsx).
		/// </param>
		/// <returns>An XML document</returns>
		public bool Load(string inputFilePath, string outputFilePath)
		{
			Uri uriWorkbook = new Uri("/xl/workbook.xml", UriKind.Relative);
			Uri uriSharedStrings = new Uri("/xl/sharedStrings.xml", UriKind.Relative);
			Uri uriStyles = new Uri("/xl/styles.xml", UriKind.Relative);
			Uri uriSheet = new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative);
			Package pack = null;

			try
			{
				pack = ZipPackage.Open(outputFilePath, FileMode.Create);

				// Create parts and relationships for the package
				PackagePart partWorkbook = pack.CreatePart(uriWorkbook, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", CompressionOption.Maximum);
				PackagePart partSharedStrings = pack.CreatePart(uriSharedStrings, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml", CompressionOption.Maximum);
				PackagePart partStyles = pack.CreatePart(uriStyles, @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml", CompressionOption.Maximum);
				PackagePart partSheet1 = pack.CreatePart(uriSheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", CompressionOption.Maximum);

				pack.CreateRelationship(partWorkbook.Uri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument");

				PackageRelationship packageRelationship = partWorkbook.CreateRelationship(partSheet1.Uri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
				partWorkbook.CreateRelationship(partSharedStrings.Uri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings");
				partWorkbook.CreateRelationship(partStyles.Uri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles");

				// create a workbook 
				StreamWriter streamWorkbook = new StreamWriter(partWorkbook.GetStream(FileMode.Create, FileAccess.Write));
				CreateWorkbookXML(packageRelationship.Id).Save(streamWorkbook);
				streamWorkbook.Close();

				// create a style part
				StreamWriter streamStyles = new StreamWriter(partStyles.GetStream(FileMode.Create, FileAccess.Write));
				CreateDataStylesXML().Save(streamStyles);
				streamStyles.Close();

				// create a new unique strings list to store shared strings
				List<string> sharedStringsOutput = new List<string>();

				// create a worksheet
				StreamWriter streamSheet1 = new StreamWriter(partSheet1.GetStream(FileMode.Create, FileAccess.Write));
				CreateWorksheetXML(sharedStringsOutput, inputFilePath).Save(streamSheet1);
				streamSheet1.Close();

				// create the shared strings part
				StreamWriter streamSharedStrings = new StreamWriter(partSharedStrings.GetStream(FileMode.Create, FileAccess.Write));
				CreateSharedStringsPart(sharedStringsOutput).Save(streamSharedStrings);
				streamSharedStrings.Close();

				pack.Flush();
				pack.Close();

				// return success
				return true;
			}
			catch
			{
				if (pack != null)
				{
					pack.Flush();
					pack.Close();
				}

				// return failure
				return false;
			}
		}


		/// <summary>
		///   Returns a new XMLDocument to add to the package
		/// </summary>
		/// <param name="SheetRelID">
		///   Sheet relation ID.
		/// </param>
		/// <returns>An XML document</returns>
		protected static XmlDocument CreateWorkbookXML(string sheetRelationshipId)
		{
			// create the workbook
			XmlDocument workbookXML = new XmlDocument();

			// create the workbook tag
			XmlElement tagWorkbook = workbookXML.CreateElement("workbook", schemaMain);

			// Add the relationships namespace
			AddSchemaAttribute(tagWorkbook, schemaRelationships, "r");
			workbookXML.AppendChild(tagWorkbook);

			// create the bookViews
			XmlElement bookViews = workbookXML.CreateElement("bookViews", schemaMain);
			tagWorkbook.AppendChild(bookViews);

			// workbook element
			XmlElement workbookView = workbookXML.CreateElement("workbookView", schemaMain);
			bookViews.AppendChild(workbookView);

			// create the sheets tag
			XmlElement tagSheets = workbookXML.CreateElement("sheets", schemaMain);
			tagWorkbook.AppendChild(tagSheets);

			// create the sheet
			XmlElement sheet1 = workbookXML.CreateElement("sheet", schemaMain);
			tagSheets.AppendChild(sheet1);

			// put sheet attributes
			sheet1.SetAttribute("name", "Main");
			sheet1.SetAttribute("sheetId", "1");
			sheet1.SetAttribute("id", schemaRelationships, sheetRelationshipId);

			return workbookXML;
		}


		/// <summary>
		///   Creates a new data styles XMLDocument for the package
		/// </summary>
		/// <returns>An XML document</returns>
		protected static XmlDocument CreateDataStylesXML()
		{
			XmlDocument stylesXML = new XmlDocument();

			// create root element to the XML document
			XmlElement tagStyleSheet = stylesXML.CreateElement("styleSheet", schemaMain);
			stylesXML.AppendChild(tagStyleSheet);

			// create number formats element
			XmlElement tagNumFmts = stylesXML.CreateElement("numFmts", schemaMain);
			tagStyleSheet.AppendChild(tagNumFmts);
			tagNumFmts.SetAttribute("count", "1");
			tagStyleSheet.AppendChild(tagNumFmts);

			// create time format element
			XmlElement tagNumFmt = stylesXML.CreateElement("numFmt", schemaMain);
			tagNumFmts.AppendChild(tagNumFmt);
			tagNumFmt.SetAttribute("numFmtId", "166");
			tagNumFmt.SetAttribute("formatCode", "[$-F400]h:mm:ss\\ AM/PM");

			// create fonts element
			XmlElement tagFonts = stylesXML.CreateElement("fonts", schemaMain);
			tagFonts.SetAttribute("count", "0");

			// default font (0)
			XmlElement tagFont = stylesXML.CreateElement("font", schemaMain);
			tagFonts.AppendChild(tagFont);

			// bold font (1)
			tagFont = stylesXML.CreateElement("font", schemaMain);
			// add underlined element
			tagFont.AppendChild(stylesXML.CreateElement("b", schemaMain));
			tagFonts.AppendChild(tagFont);

			// underlined font (2)
			tagFont = stylesXML.CreateElement("font", schemaMain);
			// add underlined element
			tagFont.AppendChild(stylesXML.CreateElement("u", schemaMain));
			tagFonts.AppendChild(tagFont);

			// bold & underlined font (3)
			tagFont = stylesXML.CreateElement("font", schemaMain);
			// add bold element
			tagFont.AppendChild(stylesXML.CreateElement("b", schemaMain));
			// add underlined element
			tagFont.AppendChild(stylesXML.CreateElement("u", schemaMain));
			tagFonts.AppendChild(tagFont);

			tagStyleSheet.AppendChild(tagFonts);

			// create fills element - don't use it, so leave empty
			XmlElement tagFills = stylesXML.CreateElement("fills", schemaMain);
			tagFills.SetAttribute("count", "0");
			tagFills.AppendChild(stylesXML.CreateElement("fill", schemaMain));
			tagStyleSheet.AppendChild(tagFills);

			// create borders element - don't use it, so leave empty
			XmlElement tagBorders = stylesXML.CreateElement("borders", schemaMain);
			tagBorders.SetAttribute("count", "0");
			tagBorders.AppendChild(stylesXML.CreateElement("border", schemaMain));
			tagStyleSheet.AppendChild(tagBorders);

			// create cell style element
			XmlElement tagCellStyleXfs = stylesXML.CreateElement("cellStyleXfs", schemaMain);
			tagCellStyleXfs.SetAttribute("count", "0");
			tagCellStyleXfs.AppendChild(stylesXML.CreateElement("xf", schemaMain));
			tagStyleSheet.AppendChild(tagCellStyleXfs);

			// create cell formats
			XmlElement tagCellXfs = stylesXML.CreateElement("cellXfs", schemaMain);
			tagCellXfs.SetAttribute("count", "3");
			tagStyleSheet.AppendChild(tagCellXfs);

			XmlElement tagXf = stylesXML.CreateElement("xf", schemaMain);

			// default cell format
			tagXf.SetAttribute("numFmtId", "0");
			tagCellXfs.AppendChild(tagXf);

			// date cell format
			tagXf = stylesXML.CreateElement("xf", schemaMain);
			tagXf.SetAttribute("numFmtId", "14");
			tagXf.SetAttribute("applyNumberFormat", "1");
			tagCellXfs.AppendChild(tagXf);

			// time cell format 
			tagXf = stylesXML.CreateElement("xf", schemaMain);
			tagXf.SetAttribute("numFmtId", "166");
			tagXf.SetAttribute("applyNumberFormat", "1");
			tagCellXfs.AppendChild(tagXf);

			// bold font (style 3)
			tagXf = stylesXML.CreateElement("xf", schemaMain);
			tagXf.SetAttribute("fontId", "1");
			tagXf.SetAttribute("applyFont", "1");
			tagCellXfs.AppendChild(tagXf);

			// underlined font (style 4 )
			tagXf = stylesXML.CreateElement("xf", schemaMain);
			tagXf.SetAttribute("fontId", "2");
			tagXf.SetAttribute("applyFont", "1");
			tagCellXfs.AppendChild(tagXf);

			// bold & underlined font (style 5)
			tagXf = stylesXML.CreateElement("xf", schemaMain);
			tagXf.SetAttribute("fontId", "3");
			tagXf.SetAttribute("applyFont", "1");
			tagCellXfs.AppendChild(tagXf);

			// create cell styles element
			XmlElement tagCellStyles = stylesXML.CreateElement("cellStyles", schemaMain);
			tagCellStyles.SetAttribute("count", "1");
			tagStyleSheet.AppendChild(tagCellStyles);

			// create single cell style
			XmlElement tagCellStyle = stylesXML.CreateElement("cellStyle", schemaMain);
			tagCellStyle.SetAttribute("name", "style1");
			tagCellStyle.SetAttribute("xfId", "0");
			tagCellStyles.AppendChild(tagCellStyle);

			// create dxfs element - don't use it, so leave empty
			XmlElement tagDxfs = stylesXML.CreateElement("dxfs", schemaMain);
			tagDxfs.SetAttribute("count", "0");
			tagStyleSheet.AppendChild(tagDxfs);

			// create table styles element - don't use it, so leave empty
			XmlElement tagTableStyles = stylesXML.CreateElement("tableStyles", schemaMain);
			tagTableStyles.SetAttribute("count", "0");
			tagStyleSheet.AppendChild(tagTableStyles);

			return stylesXML;
		}

		/// <summary>
		///   Creates an  XMLDocument filled with data for Shared Strings part of the package
		/// </summary>
		/// <param name="sharedStringsOutput">
		///   List with shared strings
		/// </param>
		protected static XmlDocument CreateSharedStringsPart(List<string> sharedStringsOutput)
		{
			XmlDocument sharedStringsXML = new XmlDocument();

			// put XML declaration
			sharedStringsXML.CreateXmlDeclaration("1.0", "UTF-8", "no");

			// create root element for the XML document
			XmlNode sst = sharedStringsXML.CreateElement("sst", schemaMain);
			sharedStringsXML.AppendChild(sst);

			// save strings list to the shared string xml
			for (int i = 0; i < sharedStringsOutput.Count; i++)
			{
				// create string item element
				XmlElement si = sharedStringsXML.CreateElement("si", schemaMain);
				sst.AppendChild(si);

				// create data element
				XmlElement data = sharedStringsXML.CreateElement("t", schemaMain);
				si.AppendChild(data);

				// craete and put data node value
				XmlNode dataNode = sharedStringsXML.CreateNode(XmlNodeType.Text, "t", schemaMain);
				dataNode.Value = sharedStringsOutput[i];
				data.AppendChild(dataNode);
			}

			return sharedStringsXML;
		}

		/// <summary>
		/// Creates main declaration of the worksheet XML and loads shared strings
		/// </summary>
		/// <param name="sharedStringsOutput">List of shared strings</param>
		/// <param name="sharedStringsSearch">Dictionary of shared strings</param>
		/// <returns>Returns sheetData XML element which data will be appended to.</returns>
		protected XmlElement PrepareWorksheetXML(List<string> sharedStringsOutput, Dictionary<string, int> sharedStringsSearch)
		{
			// worksheet xml document
			worksheetXML = new XmlDocument();

			// create XML document declaration
			worksheetXML.CreateXmlDeclaration("1.0", "UTF-8", "no");

			// create root element for the XML document
			XmlElement worksheet = worksheetXML.CreateElement("worksheet", schemaMain);
			AddSchemaAttribute(worksheet, schemaRelationships, "r");
			worksheetXML.AppendChild(worksheet);

			// create sheet data element
			XmlElement sheetData = worksheetXML.CreateElement("sheetData", schemaMain);
			worksheet.AppendChild(sheetData);

			// create row element
			XmlElement row = worksheetXML.CreateElement("row", schemaMain);
			sheetData.AppendChild(row);
			row.SetAttribute("r", "1");

			// add the headers row
			for (int columnIndex = 0; columnIndex < outputColumns.Count; columnIndex++)
			{
				// we have under 26 ouput columns to the column index will work
				string cellPosition = Convert.ToChar('A' + columnIndex) + "1";

				// add the column element
				XmlElement columnHeader = worksheetXML.CreateElement("c", schemaMain);

				// set the position
				columnHeader.SetAttribute("r", cellPosition);
				row.AppendChild(columnHeader);

				// set the type to string
				columnHeader.SetAttribute("t", "s");

				// add a value element
				XmlElement value = worksheetXML.CreateElement("v", schemaMain);
				columnHeader.AppendChild(value);

				// set the value reference to the shared strings                     
				XmlNode valueNode = worksheetXML.CreateNode(XmlNodeType.Text, "v", schemaMain);
				valueNode.Value = sharedStringsSearch.Count.ToString();
				value.AppendChild(valueNode);

				// add the column name to the shared strings dictionaries
				sharedStringsSearch.Add(outputColumns[columnIndex], sharedStringsSearch.Count);
				sharedStringsOutput.Add(outputColumns[columnIndex]);
			}

			return sheetData;
		}

		/// <summary>
		/// Writes Excel row into XML worksheet
		/// </summary>
		/// <param name="sheetData">sheetData XML element</param>
		/// <param name="currentExcelRowIndex">Index of row to append</param>
		/// <param name="outputValues">List of value to write into row</param>
		/// <param name="sharedStringsSearch">Shared strings dictionary</param>
		/// <param name="sharedStringsOutput">Shared strings list</param>
		protected void WriteRow(XmlElement sheetData, int currentExcelRowIndex, string[] outputValues, Dictionary<string, int> sharedStringsSearch, List<string> sharedStringsOutput)
		{
			// now write the output values to xml
			// add the row element
			XmlElement row = worksheetXML.CreateElement("row", schemaMain);
			sheetData.AppendChild(row);
			row.SetAttribute("r", currentExcelRowIndex.ToString());

			// add the cells
			for (int outputValueIndex = 0; outputValueIndex < outputColumns.Count; outputValueIndex++)
			{
				string outputValue = outputValues[outputValueIndex];

				// if value is in the first column (date), adjust the logging period if needed
				if (outputValueIndex == 0)
					AdjustLogDates(outputValue);

				// add the column element
				XmlElement col = worksheetXML.CreateElement("c", schemaMain);
				col.SetAttribute("r", Convert.ToChar('A' + outputValueIndex) + currentExcelRowIndex.ToString());
				row.AppendChild(col);

				// if value is a number, then put the number into the node
				ColumnType columnType = outputColumnTypes[outputValueIndex];

				if (columnType == ColumnType.Number && outputValue != "")
				{
					// insert a number node
					XmlElement value = worksheetXML.CreateElement("v", schemaMain);
					col.AppendChild(value);

					// set cell value
					XmlNode valueNode = worksheetXML.CreateNode(XmlNodeType.Text, "v", schemaMain);
					valueNode.Value = outputValue;
					value.AppendChild(valueNode);
				}
				else if (columnType == ColumnType.Date)
				{
					// insert a date node
					// set cell style to a predefined date style
					col.SetAttribute("s", "1");
					XmlElement value = worksheetXML.CreateElement("v", schemaMain);
					col.AppendChild(value);

					// set cell value
					XmlNode valueNode = worksheetXML.CreateNode(XmlNodeType.Text, "v", schemaMain);
					valueNode.Value = (Convert.ToDateTime(outputValue)).ToOADate().ToString();
					value.AppendChild(valueNode);
				}
				else if (columnType == ColumnType.Time)
				{
					// insert a time node
					// set cell style to a predefined time style
					col.SetAttribute("s", "2");
					XmlElement value = worksheetXML.CreateElement("v", schemaMain);
					col.AppendChild(value);

					// set cell value
					XmlNode valueNode = worksheetXML.CreateNode(XmlNodeType.Text, "v", schemaMain);
					valueNode.Value = (Convert.ToDateTime(outputValue)).ToOADate().ToString();
					value.AppendChild(valueNode);
				}
				else
				{
					// value is string, put the shared string reference into the node
					int sharedStringIndex = -1;
					if (!sharedStringsSearch.TryGetValue(outputValue, out sharedStringIndex))
					{
						// not found - then add to the list
						sharedStringIndex = sharedStringsSearch.Count;
						sharedStringsSearch.Add(outputValue, sharedStringsSearch.Count);
						sharedStringsOutput.Add(outputValue);
					}

					// add the string to the xml
					// indicate that cell value is a shared string using its index
					col.SetAttribute("t", "s");
					XmlElement value = worksheetXML.CreateElement("v", schemaMain);
					col.AppendChild(value);

					// set cell value as index of string in the shared strings list
					XmlNode valueNode = worksheetXML.CreateNode(XmlNodeType.Text, "v", schemaMain);
					valueNode.Value = sharedStringIndex.ToString();
					value.AppendChild(valueNode);
				}
			}
		}

		protected void AdjustLogDates(string outputValue)
		{
			DateTime rowDate;

			if (DateTime.TryParse(outputValue, out rowDate))
			{
				if (rowDate < StartDate || StartDate == DateTime.MaxValue)
					StartDate = rowDate;

				if (rowDate > EndDate || EndDate == DateTime.MinValue)
					EndDate = rowDate;
			}
		}
	}
}
